package com.jumi.microservice.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.jumi.microservice.dto.logistics.excel.BatchUpdateRealLogisticsNo;
import com.jumi.microservice.entity.LogisticsGoods;
import com.jumi.microservice.entity.SpiltMergeSetup;
import org.apache.ibatis.annotations.*;
import org.springframework.scheduling.annotation.Async;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author by mazhao
 * @Classname LogisticsGoodsMapper
 * @Description
 * @Date 2020/7/29 11:39
 */
@Mapper
public interface LogisticsGoodsMapper extends BaseMapper<LogisticsGoods> {
    /**
     * 批量插入
     *
     * @param list
     * @return
     */
    @Insert("<script>INSERT INTO logistics_goods "
            + "(logistics_no,goods_id,goods_amount,order_id,is_rule,order_no,goods_name,goods_weight,should_warehouse,real_warehouse,goods_price,order_create_time,spu_id,goods_label,self_flag,goods_cover_img,receiver_name,receiver_address,receiver_phone,is_alone,goods_max_amount,buyer_id,supplier_id,is_import,deliver_place,is_group)"
            + "VALUES "
            + "<foreach collection='list' item='l' index='index' separator=',' >"
            + "(#{l.logisticsNo},#{l.goodsId},#{l.goodsAmount},#{l.orderId},#{l.isRule},#{l.orderNo},#{l.goodsName},#{l.goodsWeight},#{l.shouldWarehouse},#{l.realWarehouse},#{l.goodsPrice},#{l.orderCreateTime},#{l.spuId},#{l.goodsLabel},#{l.selfFlag},#{l.goodsCoverImg},#{l.receiverName},#{l.receiverAddress},#{l.receiverPhone},#{l.isAlone},#{l.goodsMaxAmount},#{l.buyerId},#{l.supplierId},#{l.isImport},#{l.deliverPlace},#{l.isGroup})" +
            "</foreach></script>")
    int batchInsertGoods(List<LogisticsGoods> list);

    /**
     * 批量更新包裹号
     *
     * @param list
     * @return
     */
    @Update("<script>UPDATE logistics_goods SET" +
            " logistics_no = CASE id" +
            "<foreach collection='list' item='l' index='index'>" +
            " WHEN #{l.id} THEN #{l.logisticsNo}" +
            "</foreach>" +
            "    END" +
            " WHERE id IN (" +
            "<foreach collection='list' item='l' index='index' separator=',' >" +
            "#{l.id}" +
            "</foreach>)</script>")
    int batchUpdateLogisticsNo(List<LogisticsGoods> list);

    /**
     * 批量更新包裹号
     *
     * @param list
     * @return
     */
    @Update("<script><foreach collection='list' item='item' index='index' open='' close='' separator=';'>" +
            "UPDATE logistics_goods SET logistics_no = #{item.logisticsNo},goods_amount = #{item.goodsAmount} " +
            "where id = #{item.id} and del_flag = 0 and logistics_status = 0" +
            "</foreach></script>")
    int batchUpdateLogisticsNoById(List<LogisticsGoods> list);

    /**
     * 批量更新包裹中商品数量
     *
     * @param list
     * @return
     */
    @Update("<script>UPDATE logistics_goods SET" +
            " goods_amount = CASE id" +
            "<foreach collection='list' item='l' index='index'>" +
            " WHEN #{l.id} THEN #{l.goodsAmount}" +
            "</foreach>" +
            "    END" +
            " WHERE id IN (" +
            "<foreach collection='list' item='l' index='index' separator=',' >" +
            "#{l.id}" +
            "</foreach>)</script>")
    int batchUpdateGoodsAmount(List<LogisticsGoods> list);

    /**
     * 批量更新实发仓库
     *
     * @param list
     * @return
     */
    @Update("<script>UPDATE logistics_goods SET logistics_step = 1," +
            " real_warehouse = CASE id" +
            "<foreach collection='list' item='l' index='index'>" +
            " WHEN #{l.id} THEN #{l.realWarehouse}" +
            "</foreach>" +
            "    END" +
            " WHERE id IN (" +
            "<foreach collection='list' item='l' index='index' separator=',' >" +
            "#{l.id}" +
            "</foreach>)</script>")
    int batchUpdateRealWarehouse(List<LogisticsGoods> list);

    /**
     * 批量修改步骤标识
     *
     * @param list
     * @return
     */
    @Update("<script> update logistics_goods set logistics_step = 2 where del_flag = 0 and logistics_no in(" +
            "<foreach  collection = 'list' item = 'p' index = 'index'  separator= ',' >" +
            "#{p}" +
            "</foreach>)</script>")
    int batchUpdateLogisticsStep(List<String> list);

    /**
     * 根据物流单号批量修改删除标识
     *
     * @param list
     * @return
     */
    @Update("<script> update logistics_goods set del_flag = 1 where logistics_no in(" +
            "<foreach  collection = 'list' item = 'p' index = 'index'  separator= ',' >" +
            "#{p}" +
            "</foreach>)</script>")
    int batchUpdateDel(List<String> list);

    /**
     * 根据ID批量修改删除标识
     *
     * @param list
     * @return
     */
    @Update("<script> update logistics_goods set del_flag = 1 where id in(" +
            "<foreach  collection = 'list' item = 'p' index = 'index'  separator= ',' >" +
            "#{p}" +
            "</foreach>)</script>")
    int batchUpdateDelById(List<Integer> list);

    /**
     * 根据订单ID批量修改删除标识
     *
     * @param list
     * @return
     */
    @Update("<script> update logistics_goods set del_flag = 1 where order_id in(" +
            "<foreach  collection = 'list' item = 'p' index = 'index'  separator= ',' >" +
            "#{p.orderId}" +
            "</foreach>)</script>")
    int batchUpdateDelByOrderId(List<LogisticsGoods> list);

//    /**
//     * 根据订单ID批量修改删除标识
//     *
//     * @param list
//     * @return
//     */
//    @Update("<script> <foreach collection='list' item='item' index='index' open='' close='' separator=';'>" +
//            "update logistics_goods set del_flag = 1 where order_id = #{item.orderId} and goods_id = #{item.goodsId}" +
//            "</foreach>)</script>")
//    int batchUpdateDelByOrderIdAndGoodsId(List<LogisticsGoods> list);

    /**
     * 批量修改商品规则
     *
     * @param spiltMergeSetup
     * @return
     */
    @Update("update logistics_goods set goods_max_amount = #{spiltMergeSetup.spiltMergeCount},is_alone = #{spiltMergeSetup.isAlone} where spu_id  = #{spiltMergeSetup.spuId} and del_flag = 0 and logistics_status  = 0 and self_flag = 1 and logistics_step = 1")
    int batchUpdateRule(@Param("spiltMergeSetup") SpiltMergeSetup spiltMergeSetup);

    /**
     * 批量修改商品规则启用禁用
     *
     * @param spuId
     * @param status
     * @return
     */
    @Update("update logistics_goods set is_rule = #{status} where spu_id  = #{spuId} and del_flag = 0 and logistics_status  = 0 and self_flag = 1 and logistics_step = 1")
    int batchUpdateIsRule(@Param("spuId") Integer spuId, @Param("status") Byte status);

    /**
     * 统计某订单下某商品应发实发数量
     *
     * @param orderId
     * @param goodsId
     * @return
     */
    @Select("select ifnull(sum(goods_amount),0) as goodsAmount,ifnull(sum(real_send_amount),0) as realSendAmount from logistics_goods where order_id = #{orderId} and goods_id = #{goodsId}")
    Map<String, BigDecimal> getGoodsShouldRealSendAmount(@Param("orderId") Integer orderId, @Param("goodsId") Integer goodsId);

    /**
     * 按仓库统计
     *
     * @param startTime
     * @param endTime
     * @param warehouse
     * @return
     */
    @Select("select a.real_warehouse as warehouse,count(DISTINCT a.order_id) as orderAmount,sum(a.goods_amount) as goodsAmount from logistics_goods a inner join user_order b on b.order_status = 2 and a.order_no = b.order_no and a.del_flag = 0 and a.logistics_status  = 0 and a.self_flag = 1 and a.create_time >=#{startTime} and a.create_time<=#{endTime} and if(#{warehouse} is null,1=1,a.real_warehouse = #{warehouse}) group by a.real_warehouse")
    List<Map<String, Object>> warehouseStatistics(@Param("warehouse") Integer warehouse, @Param("startTime") Date startTime, @Param("endTime") Date endTime);

    /**
     * 按SPU统计
     *
     * @param warehouse
     * @param startTime
     * @param endTime
     * @param page
     * @param goodsName
     * @return
     */
    @Select("select a.spu_id as spuId, count(DISTINCT a.order_id) as orderAmount,sum(a.goods_amount) as goodsAmount from logistics_goods a inner join user_order b on a.order_no = b.order_no and b.order_status = 2 and a.del_flag = 0 and a.logistics_status  = 0 and a.self_flag = 1 and a.create_time >=#{startTime} and a.create_time<=#{endTime} and a.real_warehouse = #{warehouse} and if(#{goodsName} is null,1=1,a.goods_name Like #{goodsName}) group by a.spu_id order by orderAmount desc")
    IPage<Map<String, Object>> goodsSpuStatistics(@Param("warehouse") Integer warehouse, @Param("goodsName") String goodsName, @Param("startTime") Date startTime, @Param("endTime") Date endTime, Page<Map<String, BigDecimal>> page);

    /**
     * 批量导入发货
     *
     * @param list
     * @return
     */
    @Update("<script><foreach collection='list' item='item' index='index' open='' close='' separator=';'>" +
            "update logistics_goods set " +
            "real_logistics_no = #{item.realLogisticsNo},express_code = #{item.expressCode}," +
            "real_send_amount = #{item.realSendAmount},logistics_status = #{item.logisticsStatus},remark = #{item.remark} " +
            "where order_no = #{item.orderNo} and goods_id = #{item.goodsId} and logistics_no = #{item.logisticsNo}" +
            " and del_flag = 0 and logistics_status = 0" +
            "</foreach></script>")
    int updateSend(List<LogisticsGoods> list);

    /**
     * 批量发货
     *
     * @param list
     * @return
     */
    @Update("<script><foreach collection='list' item='item' index='index' open='' close='' separator=';'>" +
            "update logistics_goods set " +
            "real_logistics_no = #{item.realLogisticsNo},express_code = #{item.expressCode}," +
            "real_send_amount = #{item.realSendAmount},logistics_status = #{item.logisticsStatus},remark = #{item.remark} " +
            "where order_no = #{item.orderNo} and goods_id = #{item.goodsId} and del_flag = 0 and logistics_status = 0" +
            "</foreach></script>")
    int noSelfSend(List<LogisticsGoods> list);

    /**
     *
     * @param list
     * @return
     */
    @Update("<script><foreach collection='list' item='item' index='index' open='' close='' separator=';'>" +
            "update logistics_goods set " +
            "real_logistics_no = #{item.realLogisticsNo},express_code = #{item.expressCode},goods_amount = #{item.goodsAmount},logistics_status = #{item.logisticsStatus},real_send_amount = #{item.realSendAmount},remark = #{item.remark}" +
            "where id = #{item.id}" +
            "</foreach></script>")
    int partSend(List<LogisticsGoods> list);

    /**
     * 批量插入
     *
     * @param list
     * @return
     */
    @Insert("<script>INSERT INTO logistics_goods "
            + "(real_logistics_no,express_code,is_wdt_push,real_send_amount,logistics_status,logistics_no,goods_id,goods_amount,order_id,is_rule,order_no,goods_name,goods_weight,should_warehouse,real_warehouse,goods_price,order_create_time,spu_id,goods_label,self_flag,goods_cover_img,receiver_name,receiver_address,receiver_phone,is_alone,goods_max_amount,buyer_id,supplier_id,is_import,deliver_place,is_group,remark)"
            + "VALUES "
            + "<foreach collection='list' item='l' index='index' separator=',' >"
            + "(#{l.realLogisticsNo},#{l.expressCode},#{l.isWdtPush},#{l.realSendAmount},#{l.logisticsStatus},#{l.logisticsNo},#{l.goodsId},#{l.goodsAmount},#{l.orderId},#{l.isRule},#{l.orderNo},#{l.goodsName},#{l.goodsWeight},#{l.shouldWarehouse},#{l.realWarehouse},#{l.goodsPrice},#{l.orderCreateTime},#{l.spuId},#{l.goodsLabel},#{l.selfFlag},#{l.goodsCoverImg},#{l.receiverName},#{l.receiverAddress},#{l.receiverPhone},#{l.isAlone},#{l.goodsMaxAmount},#{l.buyerId},#{l.supplierId},#{l.isImport},#{l.deliverPlace},#{l.isGroup},#{l.remark})" +
            "</foreach></script>")
    int batchInsertGoodsPartSend(List<LogisticsGoods> list);
    /**
     * 批量导出查询
     *
     * @param goodsName
     * @param orderNoList
     * @param benchmarkTime
     * @return
     */
    @Select("<script>" +
            "select * from logistics_goods a ," +
            "(select count(*) as count,logistics_no from logistics_goods " +
            "where del_flag = 0 and self_flag = 1 and logistics_status = 0 " +
            "and create_time &lt;= #{benchmarkTime} " +
            "<if test = 'goodsName !=null and goodsName.trim()!=&quot;&quot;'> " +
            "and goods_name like CONCAT('%',#{goodsName},'%')" +
            "</if> " +
            "<if test = 'orderNoList!=null and orderNoList.trim()!=&quot;&quot;'>" +
            "and order_no in ( ${orderNoList} )" +
            "</if>  " +
            "group by logistics_no) b " +
            "where a.logistics_no = b.logistics_no " +
            "<if test = 'goodsName !=null and goodsName.trim()!=&quot;&quot;'> " +
            "and a.goods_name like CONCAT('%',#{goodsName},'%')" +
            "</if> " +
            "<if test = 'orderNoList!=null and orderNoList.trim()!=&quot;&quot;'>" +
            "and a.order_no in ( ${orderNoList} )" +
            "</if> " +
            "and a.del_flag = 0 and a.self_flag = 1 and a.logistics_status = 0 and a.is_wdt_push = 0 " +
            "and a.create_time &lt;= #{benchmarkTime} ORDER BY b.count desc</script>")
    List<LogisticsGoods> exportLogisticsGoods(@Param("goodsName") String goodsName, @Param("orderNoList") String orderNoList, @Param("benchmarkTime") Date benchmarkTime);

    /**
     * 批量发货修改运单号以及快递公司及物流状态
     *
     * @param list
     * @return
     */
    @Update("<script><foreach collection='list' item='item' index='index' open='' close='' separator=';'>" +
            "UPDATE logistics_goods SET real_logistics_no = #{item.realLogisticsNo},express_code = #{item.expressCode},logistics_status = #{item.logisticsStatus},real_send_amount = goods_amount" +
            " where logistics_no = #{item.logisticsNo} and del_flag = 0 and logistics_status = 0" +
            "</foreach></script>")
    int batchUpdateSend(List<LogisticsGoods> list);

    /**
     * 批量发货修改运单号以及快递公司及物流状态
     *
     * @param list
     * @return
     */
    @Update("<script><foreach collection='list' item='item' index='index' open='' close='' separator=';'>" +
            "UPDATE logistics_goods SET real_logistics_no = #{item.realLogisticsNo},logistics_company = #{item.logisticsCompany},logistics_status = #{item.logisticsStatus},real_send_amount = #{item.realSendAmount}" +
            " where order_no = #{item.order_no} and goods_id = #{item.goodsId} and del_flag = 0 and logistics_status = 0" +
            "</foreach></script>")
    int batchUpdateSendByOrder(List<LogisticsGoods> list);

    /**
     * 批量修改运单号以及快递公司
     *
     * @param list
     * @return
     */
    @Update("<script><foreach collection='list' item='item' index='index' open='' close='' separator=';'>" +
            "UPDATE logistics_goods SET real_logistics_no = #{item.newRealLogisticsNo},express_code = #{item.expressCompany} " +
            " where real_logistics_no = #{item.oldRealLogisticsNo} and order_no = #{item.orderNo}" +
            "</foreach></script>")
    int batchUpdateRealLogisticsNo(List<BatchUpdateRealLogisticsNo> list);
}
